use office::{Excel, DataType};
use std::io::prelude::*;
use std::fs::OpenOptions;

fn main()-> std::io::Result<()> {
    let mut file = OpenOptions::new()
        .append(true).open("D:\\text.sql")?;
    // opens a new workbook
    let path = "./file/文学历史.xlsx";
    let mut excel  = Excel::open(path).unwrap();
    let r = excel.worksheet_range("历史典故").unwrap();
    let mut rid = 5747;//rid 起始值
    let eid = "60".to_string();// 60 书籍摘抄
    let tid = "10".to_string();// 9 文学历史-历史典故 15 文学历史-文学 16 文学历史-名人简介

    for row in r.rows() {
        let mut title = String::new();
        let mut content = String::new();
        for (i, c) in row.iter().enumerate() {
            if i==0 {
                match *c {
                    DataType::String(ref s) => title = s.parse().unwrap(),
                    _ => (),
                };
            }else if i == 3{
                match *c {
                    DataType::String(ref s) => content = s.parse().unwrap(),
                    _ => (),
                };
            }
        }
        if title.len()>0 {
            //INSERT INTO `resource`.`t_resource`(`rid`, `title`, `eid`, `tid`, `warehouse`, `cid`, `ctime`, `uid`, `utime`, `reading`, `status`, `remarks`, `warehousecode`) VALUES (40758, '国际残奥委会是什么样的组织？', 60, 1, '知识科普库', 113, '2020-06-10 00:00:00', 113, '2020-06-12 12:38:39', NULL, 2, '', '1');
            let mut sql1 = "INSERT INTO `resource`.`t_resource`(`rid`, `title`, `eid`, `tid`, `warehouse`, `cid`, `ctime`, `uid`, `utime`, `reading`, `status`, `remarks`, `warehousecode`) VALUES (".to_string();
            sql1.push_str(&rid.to_string());
            sql1.push_str(",'");
            sql1.push_str(&title);
            sql1.push_str("',");
            sql1.push_str(&eid);
            sql1.push_str(",");
            sql1.push_str(&tid);
            sql1.push_str(", '知识科普库', 1, '2020-06-10 00:00:00', 1, '2020-06-12 12:38:39', NULL, 2, '', '1');\n");


            //println!("{}",sql1);

            file.write(sql1.as_ref())?;


            //INSERT INTO `resource`.`t_resource_expansion_value2`(`eid`, `fid`, `rid`, `value`, `hashvalue`, `cid`, `ctime`, `uid`, `utime`, `status`, `remarks`) VALUES (30401, 117, 41600, '三、国际聋人体育联合会（CISs）。成立于1924年，总部设在法国。共宗旨是：“通过体育达到平等”，并积组织聋人体育竟赛。每四年举办一届世界人运动会，到目前为止已举行了20屈。该联合会共有45个会员，我国聋人体育协会是国际聋人体有联合会的正式会员。国际聋人体有联合会的网址是www.ciss.org。<br />', NULL, 116, '2020-01-15 21:52:47', NULL, NULL, 0, NULL);
            let mut sql2 = "INSERT INTO `resource`.`t_resource_expansion_value2`(`eid`, `fid`, `rid`, `value`, `hashvalue`, `cid`, `ctime`, `uid`, `utime`, `status`, `remarks`) VALUES (".to_string();
            sql2.push_str(&rid.to_string());
            sql2.push_str(",117,");
            sql2.push_str(&rid.to_string());
            sql2.push_str(",'");
            sql2.push_str(&content.replace("'",""));
            sql2.push_str("',NULL, 1, '2020-01-15 21:52:47', NULL, NULL, 0, NULL);\n");
            file.write(sql2.as_ref())?;
            rid+=1;
        }
    }


    println!("max-rid:{}",rid);
    Ok(())
}
